package com.poi.demopoi.controller2;

import org.apache.commons.lang3.ArrayUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;

//废弃
public class PoiU3 {

    private static final String EXCEL_XLS = "xls";
    private static final String EXCEL_XLSX = "xlsx";
    static String format = "yyyy年MM月dd日 HH时mm分ss秒 ";

    /**
     * 读取Excel的值，对应到T对象上,T属性的顺序对应的Excel的列的顺序
     * 数据默认是有表头的，且读数据时将从表头的下一行开始读取
     *
     * @param sheet    sheet表
     * @param clazzRes 生成的VO类，一定要有set，get方法
     * @return Vo集合
     * @paramss <T> Vo类
     */


    public static <T> List<T> readSheet(Sheet sheet, T clazzRes, String... strArr) {
        if (null == sheet) {
            return null;
        }
        Class clazz = clazzRes.getClass();
        List<T> list = new ArrayList<>();
        //获取类属性信息
        Field[] fields = clazz.getDeclaredFields();
        try {
            for (int rowNum = sheet.getFirstRowNum() + 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (null == row) {
                    continue;
                }
                T object = (T) clazz.newInstance();
                for (int j = 0; j < fields.length; j++) {
                    if (!ArrayUtils.contains(strArr, fields[j].getName())) {
                        continue;
                    }
                    fields[j].setAccessible(true);
                    String value = getCellValue(sheet, rowNum, j);
                    Method method = null;
                    //对应的属性类型
                    switch (fields[j].getType().toString()) {
                        case "class java.lang.String":
                            method = clazz.getMethod(getSetMethodName(fields[j].getName()), String.class);
                            method.invoke(object, value);
                            break;
                        case "int":
                        case "class java.lang.Integer":
                            method = clazz.getMethod(getSetMethodName(fields[j].getName()), Integer.class);
                            method.invoke(object, Integer.valueOf(value).intValue());
                            break;
                        case "long":
                        case "class java.lang.Long":
                            method = clazz.getMethod(getSetMethodName(fields[j].getName()), Long.class);
                            method.invoke(object, Long.valueOf(value).longValue());
                            break;
                        case "double":
                        case "class java.lang.Double":
                            method = clazz.getMethod(getSetMethodName(fields[j].getName()), Double.class);
                            method.invoke(object, Double.valueOf(value).doubleValue());
                            break;
                        case "short":
                        case "class java.lang.Short":
                            method = clazz.getMethod(getSetMethodName(fields[j].getName()), Short.class);
                            method.invoke(object, Short.valueOf(value).shortValue());
                            break;
                        case "boolean":
                        case "class java.lang.Boolean":
                            method = clazz.getMethod(getSetMethodName(fields[j].getName()), Boolean.class);
                            method.invoke(object, Boolean.valueOf(value));
                            break;
                        case "byte":
                        case "class java.lang.Byte":
                            method = clazz.getMethod(getSetMethodName(fields[j].getName()), Byte.class);
                            method.invoke(object, Byte.valueOf(value).byteValue());
                            break;
                        case "class java.util.Date":
                            method = clazz.getMethod(getSetMethodName(fields[j].getName()), Date.class);
                            Date date = getDate(value, format);
                            method.invoke(object, date);
                            break;

                        default:
                            break;
                    }
                }
                list.add(object);

            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return list;
    }

    /**
     * 创建workbook
     *
     * @param excelType 创建excel的类型，xls或xlsx
     * @return workbook
     */
    public Workbook createWorkbook(String excelType) {
        Workbook wb;
        if (EXCEL_XLS.equals(excelType)) {
            wb = new HSSFWorkbook();
        } else if (EXCEL_XLSX.equals(excelType)) {
            wb = new XSSFWorkbook();
        } else {
            return null;
        }
        return wb;
    }

    /**
     * 创建Excel，Data中包括sheet名称、headers表头，list为数据
     * 表头的顺序一定要和list对象中的顺序一样
     *
     * @param wb   workbook
     * @param data sheet数据
     */
    public Workbook createSheet(Workbook wb, ExcelData data) {
        if (null == wb) {
            return null;
        }
        int startCell = 0;
        int startRow = 0;
        if (data.getStartCell() < 1) {
            startCell = 0;
        } else {
            startCell = data.getStartCell() - 1;
        }

        if (data.getStartRow() < 1) {
            startRow = 0;
        } else {
            startRow = data.getStartRow() - 1;
        }

        //设置sheet名字
        Sheet sheet = wb.createSheet(data.getSheetName());
        Row row = null;
        row = sheet.createRow(startRow);
        row.setHeightInPoints(20);
        for (int i = 0; i < data.getHeaders().length; i++) {
            row.createCell(i + startCell, CellType.STRING).setCellValue(data.getHeaders()[i]);
        }

        if (null == data.getList() || data.getList().size() == 0) {
            return wb;
        }

        Method method = null;
        Class clazz = data.getList().get(0).getClass();
        Field[] fields = clazz.getDeclaredFields();
        Cell cell;
        Object object;
        try {
            for (int i = 0; i < data.getList().size(); i++) {

                row = sheet.createRow(i + 1 + startRow);
                for (int j = 0; j < fields.length; j++) {
                    fields[j].setAccessible(true);
                    method = clazz.getDeclaredMethod(getGetMethodName(fields[j].getName()));
                    method.setAccessible(true);
                    object = method.invoke(data.getList().get(i));
                    switch (fields[j].getType().toString()) {
                        case "class java.lang.String":
                            cell = row.createCell(j + startCell, CellType.STRING);
                            cell.setCellValue(object.toString());
                            break;
                        case "int":
                        case "class java.lang.Integer":
                            cell = row.createCell(j + startCell, CellType.NUMERIC);
                            cell.setCellValue((int) object);
                            break;
                        case "double":
                        case "class java.lang.Double":
                            cell = row.createCell(j + startCell, CellType.NUMERIC);
                            cell.setCellValue((double) object);
                            break;
                        case "float":
                        case "class java.lang.Float":
                            cell = row.createCell(j + startCell, CellType.NUMERIC);
                            cell.setCellValue((float) object);
                            break;
                        case "short":
                        case "class java.lang.Short":
                            cell = row.createCell(j + startCell, CellType.NUMERIC);
                            cell.setCellValue((short) object);
                            break;
                        case "byte":
                        case "class java.lang.Byte":
                            cell = row.createCell(j + startCell, CellType.NUMERIC);
                            cell.setCellValue((byte) object);
                            break;
                        case "class java.util.Date":
                            cell = row.createCell(j + startCell, CellType.NUMERIC);
                            cell.setCellValue(getDateStr((Date) object, format));
                            break;
                        case "boolean":
                        case "class java.lang.Boolean":
                            cell = row.createCell(j + startCell, CellType.BOOLEAN);
                            cell.setCellValue((boolean) object);
                            break;
                        default:
                            break;
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return wb;
    }

    /**
     * 读取单元格的值
     *
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public static String getCellValue(Sheet sheet, int row, int column) {
        String value = null;
        if (isMergedRegion(sheet, row, column)) {
            value = getMergedRegionValue(sheet, row, column);
        } else {
            Row rowData = sheet.getRow(row);
            Cell cell = rowData.getCell(column);
            value = getCellValue(cell);
        }
        return value;
    }

    /**
     * 获取cell值，将所有的数据转化为String值
     *
     * @param cell
     * @return
     */
    private static String getCellValue(Cell cell) {
        if (null == cell) {
            return "";
        }
        if (cell.getCellTypeEnum() == CellType.STRING) {

            return cell.getStringCellValue();

//        } else if (cell.getCellType() == CellType.BOOLEAN) {
        } else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {

            return String.valueOf(cell.getBooleanCellValue());

        } else if (cell.getCellTypeEnum() == CellType.FORMULA) {

            try {
                return cell.getCellFormula();
            } catch (Exception e) {
                return String.valueOf(cell.getNumericCellValue());
            }

        } else if (cell.getCellTypeEnum() == CellType.NUMERIC) {

            if (DateUtil.isCellDateFormatted(cell)) {
                SimpleDateFormat sdf = new SimpleDateFormat(format);
                return sdf.format(cell.getDateCellValue());
            }
            return String.valueOf(cell.getNumericCellValue());

        } else {
            cell.setCellType(CellType.STRING);
            return cell.getStringCellValue();
        }
    }

    /**
     * 判断指定的单元格是否是合并单元格
     *
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    private static boolean isMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {

                    return true;
                }
            }
        }

        return false;
    }

    /**
     * 获取合并单元格的值
     *
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    private static String getMergedRegionValue(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if (row >= firstRow && row <= lastRow) {

                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell);
                }
            }
        }

        return null;
    }


    /**
     * 日期格式化
     *
     * @param dateStr 日期字符串
     * @param format  日期格式
     * @return Date日期
     */
    private static Date getDate(String dateStr, String format) {
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat(format);
        try {
            return simpleDateFormat.parse(dateStr);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 根据Date转str
     *
     * @param date   日期
     * @param format 日期格式
     * @return 日期字符串
     */
    private String getDateStr(Date date, String format) {
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat(format);
        return simpleDateFormat.format(date);
    }

    /**
     * 反射属性的set方法名
     *
     * @param fieldStr 属性名字
     * @return 属性对应的set方法
     */
    private static String getSetMethodName(String fieldStr) {
        return "set" + fieldStr.substring(0, 1).toUpperCase() + fieldStr.substring(1);
    }

    /**
     * 反射属性的get方法名
     *
     * @param fieldStr 属性名字
     * @return 属性对应的get方法
     */
    private String getGetMethodName(String fieldStr) {
        return "get" + fieldStr.substring(0, 1).toUpperCase() + fieldStr.substring(1);
    }


/////////////////////////////////////////////////////这是ＥＸＣＥＬDATE////////////////////////////////

    public class ExcelData {

        /**
         * sheet名字
         */
        private String sheetName;
        /**
         * 表头数据
         */
        private String[] headers;
        /**
         * 数据
         */
        private List<?> list;
        /**
         * 开始行
         */
        private Integer startRow;
        /**
         * 开始列
         */
        private Integer startCell;

        public Integer getStartRow() {
            return startRow;
        }

        public void setStartRow(Integer startRow) {
            this.startRow = startRow;
        }

        public Integer getStartCell() {
            return startCell;
        }

        public void setStartCell(Integer startCell) {
            this.startCell = startCell;
        }

        public String getSheetName() {
            return sheetName;
        }

        public void setSheetName(String sheetName) {
            this.sheetName = sheetName;
        }

        public String[] getHeaders() {
            return headers;
        }

        public void setHeaders(String[] headers) {
            this.headers = headers;
        }

        public List<?> getList() {
            return list;
        }

        public void setList(List<?> list) {
            this.list = list;
        }
    }


}
